HW#5 --- last modified March 02 2019 21:18:03..

Solution set.

Due date: May 17

Files to be submitted:
  ObjectOriented.sql
  MusicBand.dtd
  IsolationTest.java
  Hw5Problems.pdf

Purpose: To learn more about concurrency control. To experiment with locking in Oracle. To experiment a little with OO and XML in Oracle.

Specification:

To begin do problems 9.2.4, 9.4.3, 9.5.2, 9.8.2 out of the book and submit them in the file Hw5Problems.pdf .

For the coding part of the homework, I would like you to create a table in Oracle called MusicBand. This table should have three columns: Name, BandFacts, Discography. Name should be a varchar. BandFacts should be a composite type made using create type. Finally, Discography should be of XMLTYPE and you should define a suitable DTD for listing a sequence of record albums. (Note: Oracle 8i without a patch doesn't support XMLTYPE -- I had forgotten this. So if you are doing this on the school database make the DTD, but only use a create type like BandFacts for this column.) Insert half a dozen test rows into this table. See the HW5 solutions from my 2003 version of this class for examples of using create type and XMLTYPE as well as inserting into the resulting table. Submit your script for creating the table and inserting the rows as ObjectOriented.sql . It should be noted that you aren't getting Oracle to do validation against your DTD, you are creating a DTD to say you have done this once and to understand that such validation would be possible.

Next write a Java program IsolationTest.java which opens two separate connections to the Oracle database at school. (If you write your program with your Oracle server at home, you should make sure it is clear how to get your program to work with the schools database by having a comment at the start of your code explaining what needs to be changed.) Set the isolation level for one connection to be read committed. Do this directly with an executeUpdate. Then do a sequence of interleaving operations between the two connections, printing to the screen what you are doing at each step. Your interleaving operations should be such that the connection which is in the read committed isolation level only performs reads, reads the same row twice, but sees a different value each time. This is called an unrepeatable read. Next have your program close and reestablish the two connections. For the connection which only performed reads the first time, now set the isolation level to serializable. Have the program do the same interleaving now as the first time, printing the results to the screen. This time the read should be the same in both cases. What this experiment illustrates is how long Oracle holds locks automatically obtained for a transaction is dependent on the isolation level. In the case of the read committed isolation level, Oracle can release the shared lock right after the operation. It does not wait until the end of the transaction.

Point Breakdown